package sk.vasicek.drinkmanager.service;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import sk.vasicek.drinkmanager.domain.CustomDrink;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class CustomDrinkHelper extends SQLiteOpenHelper {

	private static final String TAG = CustomDrinkHelper.class.getSimpleName();
	private static final int DATABASE_VERSION = 3;
	private static final String DATABASE_NAME = "custom_drinks_db";
	public static final String ID = "drink_id";
	public static final String DRINK_NAME = "drink_name";
	private static final String NOTE = "drink_note";
	public static final String ALCOHOL_CONCENTRATION = "concentration";
	public static final String VOLUME = "volume";
	public static final String TIME_CREATED = "time_drinked";
	public static final String PRICE = "price";
	private static final String CUSTOM_DRINK_TABLE_NAME = "custom_drinks";
	private static final String CUSTOM_DRINKS_TABLE_CREATE = "CREATE TABLE " + CUSTOM_DRINK_TABLE_NAME + " (" + ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + DRINK_NAME + " TEXT, " + NOTE + " TEXT, "
			+ PRICE + " DOUBLE, " + ALCOHOL_CONCENTRATION + " DOUBLE, " + VOLUME + " DOUBLE, " + TIME_CREATED + " TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);";
	private static final String CUSTOM_DRINKS_TABLE_DROP = "DROP TABLE " + CUSTOM_DRINK_TABLE_NAME + ";";

	public CustomDrinkHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CUSTOM_DRINKS_TABLE_CREATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Version 0.7.8.0 -> 0.7.8.5
		if (oldVersion == 3 && newVersion == 4) {
			ArrayList<CustomDrink> drinkPoints = new ArrayList<CustomDrink>();
			Cursor cursor = db.query(CUSTOM_DRINK_TABLE_NAME, new String[] { TIME_CREATED, DRINK_NAME, ALCOHOL_CONCENTRATION, VOLUME }, null, null, null, null, TIME_CREATED + " DESC");
			while (cursor.getCount() > 0 && cursor.moveToNext()) {
				String place = cursor.getString(cursor.getColumnIndex(DRINK_NAME));
				Timestamp timeCreated = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME_CREATED)));
				double latitude = cursor.getDouble(cursor.getColumnIndex(ALCOHOL_CONCENTRATION));
				double longitude = cursor.getDouble(cursor.getColumnIndex(VOLUME));
				CustomDrink drinkPoint = new CustomDrink(latitude, longitude, place, "Location: " + String.valueOf(latitude).subSequence(0, 6) + " " + String.valueOf(longitude).subSequence(0, 6)
						+ " " + timeCreated.getDate() + "." + timeCreated.getMonth() + ". " + timeCreated.getHours() + ":" + timeCreated.getMinutes() + ":" + timeCreated.getSeconds());
				drinkPoints.add(drinkPoint);
			}

			db.execSQL(CUSTOM_DRINKS_TABLE_DROP);
			db.execSQL(CUSTOM_DRINKS_TABLE_CREATE);
			for (CustomDrink drinkEntry : drinkPoints) {
				ContentValues contentValues = new ContentValues();
				String nameEscaped = StringHelper.avoidInjectionString(drinkEntry.getName());
				contentValues.put(DRINK_NAME, nameEscaped);
				String noteEscaped = StringHelper.avoidInjectionString(drinkEntry.getNote());
				contentValues.put(NOTE, noteEscaped);
				contentValues.put(PRICE, 0);
				contentValues.put(ALCOHOL_CONCENTRATION, drinkEntry.getConcentration());
				contentValues.put(VOLUME, drinkEntry.getVolume());

				db.insert(CUSTOM_DRINK_TABLE_NAME, null, contentValues);
			}
		} else {
			db.execSQL(CUSTOM_DRINKS_TABLE_DROP);
			db.execSQL(CUSTOM_DRINKS_TABLE_CREATE);
		}
	}

	public ArrayList<CustomDrink> getAllCustomDrinks() {
		SQLiteDatabase db = getReadableDatabase();
		ArrayList<CustomDrink> drinkPoints = new ArrayList<CustomDrink>();
		Cursor cursor = db.query(CUSTOM_DRINK_TABLE_NAME, new String[] { ID, TIME_CREATED, DRINK_NAME, NOTE, PRICE, ALCOHOL_CONCENTRATION, VOLUME }, null, null, null, null, TIME_CREATED + " DESC");
		while (cursor.getCount() > 0 && cursor.moveToNext()) {

			Integer id = cursor.getInt(cursor.getColumnIndex(ID));
			String name = cursor.getString(cursor.getColumnIndex(DRINK_NAME));
			String note = cursor.getString(cursor.getColumnIndex(NOTE));
			double price = cursor.getDouble(cursor.getColumnIndex(PRICE));
			Timestamp timeDrinked = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME_CREATED)));
			Date time = new Date(timeDrinked.getTime());
			double concentration = cursor.getDouble(cursor.getColumnIndex(ALCOHOL_CONCENTRATION));
			double volume = cursor.getDouble(cursor.getColumnIndex(VOLUME));
			CustomDrink drinkPoint = new CustomDrink(id, concentration, volume, name, note, price, time);
			drinkPoints.add(drinkPoint);
		}
		db.close();
		return drinkPoints;
	}

	public ArrayList<CustomDrink> getAllCustomDrinksAfterID(Long afterId) {
		SQLiteDatabase db = getReadableDatabase();
		ArrayList<CustomDrink> drinkPoints = new ArrayList<CustomDrink>();
		Cursor cursor = db.query(CUSTOM_DRINK_TABLE_NAME, new String[] { ID, TIME_CREATED, DRINK_NAME, NOTE, PRICE, ALCOHOL_CONCENTRATION, VOLUME }, ID + " > " + afterId.toString(), null, null, null,
				TIME_CREATED + " DESC");
		while (cursor.getCount() > 0 && cursor.moveToNext()) {

			Integer id = cursor.getInt(cursor.getColumnIndex(ID));
			String name = cursor.getString(cursor.getColumnIndex(DRINK_NAME));
			String note = cursor.getString(cursor.getColumnIndex(NOTE));
			double price = cursor.getDouble(cursor.getColumnIndex(PRICE));
			Timestamp timeDrinked = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME_CREATED)));
			Date time = new Date(timeDrinked.getTime());
			double concentration = cursor.getDouble(cursor.getColumnIndex(ALCOHOL_CONCENTRATION));
			double volume = cursor.getDouble(cursor.getColumnIndex(VOLUME));
			CustomDrink drinkPoint = new CustomDrink(id, concentration, volume, name, note, price, time);
			drinkPoints.add(drinkPoint);
		}
		db.close();
		return drinkPoints;
	}

	public long insertCustomDrink(CustomDrink drinkEntry) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put(ALCOHOL_CONCENTRATION, drinkEntry.getConcentration());
		contentValues.put(VOLUME, drinkEntry.getVolume());
		String nameEscaped = StringHelper.avoidInjectionString(drinkEntry.getName());
		contentValues.put(DRINK_NAME, nameEscaped);
		String noteEscaped = StringHelper.avoidInjectionString(drinkEntry.getNote());
		contentValues.put(NOTE, noteEscaped);
		contentValues.put(PRICE, drinkEntry.getPrice());
		long id = db.insert(CUSTOM_DRINK_TABLE_NAME, null, contentValues);
		db.close();
		return id;
	}

	public void insertCustomDrink(String name, String note, double rating, double latitude, double longitude) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put(ALCOHOL_CONCENTRATION, latitude);
		contentValues.put(VOLUME, longitude);
		String nameEscaped = StringHelper.avoidInjectionString(name);
		contentValues.put(DRINK_NAME, nameEscaped);
		String noteEscaped = StringHelper.avoidInjectionString(note);
		contentValues.put(NOTE, noteEscaped);
		contentValues.put(PRICE, rating);
		db.insert(CUSTOM_DRINK_TABLE_NAME, null, contentValues);
		db.close();
	}

	public void updateCustomDrink(Integer id, String name, String note, double price, double concentration, double volume) {
		if (id != null && !"".equals(id.toString())) {
			SQLiteDatabase db = getWritableDatabase();
			ContentValues contentValues = new ContentValues();
			String nameEscaped = StringHelper.avoidInjectionString(name);
			contentValues.put(DRINK_NAME, nameEscaped);
			String noteEscaped = StringHelper.avoidInjectionString(note);
			contentValues.put(NOTE, noteEscaped);
			contentValues.put(PRICE, price);
			contentValues.put(ALCOHOL_CONCENTRATION, concentration);
			contentValues.put(VOLUME, volume);
			db.update(CUSTOM_DRINK_TABLE_NAME, contentValues, ID + " = " + id.toString(), null);
			db.close();
		}
	}

	public void updateCustomDrinkOrInsert(Integer id, CustomDrink customDrink) {
		CustomDrink drink = null;
		try {
			drink = getCustomDrinkByName(customDrink.getName());
		} catch (Exception ex) {
			Log.e(TAG, "Failed to read database", ex);
		}
		if (drink != null)
			updateCustomDrink(id, customDrink.getName(), customDrink.getNote(), customDrink.getPrice(), customDrink.getConcentration(), customDrink.getVolume());
		else
			insertCustomDrink(customDrink.getName(), customDrink.getNote(), customDrink.getPrice(), customDrink.getConcentration(), customDrink.getVolume());
	}

	public void removeCustomDrink(Integer id) {
		if (id != null && !"".equals(id.toString()) && id > 3) {
			SQLiteDatabase db = getWritableDatabase();

			db.delete(CUSTOM_DRINK_TABLE_NAME, ID + " = " + id.toString(), null);
			db.close();
		}
	}

	public void removeCustomDrink(CustomDrink drinkPoint) {
		if (drinkPoint != null && drinkPoint.getId() != null && !"".equals(drinkPoint.getId()) && drinkPoint.getId() > 3) {
			SQLiteDatabase db = getWritableDatabase();

			db.delete(CUSTOM_DRINK_TABLE_NAME, ID + " = " + drinkPoint.getId().toString(), null);
			db.close();
		}
	}

	// public void removeCustomDrink(Integer id, CustomDrink customDrink) {
	// if (id != null && !"".equals(id.toString())) {
	// SQLiteDatabase db = getWritableDatabase();
	// ContentValues contentValues = new ContentValues();
	// contentValues.put(DRINK_NAME, customDrink.getName());
	// contentValues.put(NOTE, customDrink.getNote());
	// contentValues.put(PRICE, customDrink.getPrice());
	// db.update(CUSTOM_DRINK_TABLE_NAME, contentValues, ID + " = " +
	// id.toString(), null);
	// db.close();
	// }
	// }

	public CustomDrink getCustomDrinkByID(Integer id) {
		if (id != null && !"".equals(id.toString())) {
			SQLiteDatabase db = getReadableDatabase();
			Cursor cursor = db.query(CUSTOM_DRINK_TABLE_NAME, new String[] { TIME_CREATED, DRINK_NAME, NOTE, PRICE, ALCOHOL_CONCENTRATION, VOLUME }, ID + " = " + id.toString(), null, null, null,
					TIME_CREATED + " DESC");
			CustomDrink customDrink = null;
			if (cursor.getCount() > 0 && cursor.getCount() == 1 && cursor.moveToNext()) {
				String place = cursor.getString(cursor.getColumnIndex(DRINK_NAME));
				String note = cursor.getString(cursor.getColumnIndex(NOTE));
				double rating = cursor.getDouble(cursor.getColumnIndex(PRICE));
				Timestamp timeOfLocation = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME_CREATED)));
				Date time = new Date(timeOfLocation.getTime());
				double latitude = cursor.getDouble(cursor.getColumnIndex(ALCOHOL_CONCENTRATION));
				double longitude = cursor.getDouble(cursor.getColumnIndex(VOLUME));
				customDrink = new CustomDrink(id, latitude, longitude, place, note, rating, time);
			}
			db.close();
			return customDrink;
		}
		return null;
	}

	public CustomDrink getCustomDrinkByName(String name) {
		if (name != null && !"".equals(name)) {
			SQLiteDatabase db = getReadableDatabase();
			Cursor cursor = db.query(CUSTOM_DRINK_TABLE_NAME, new String[] { ID, TIME_CREATED, DRINK_NAME, NOTE, PRICE, ALCOHOL_CONCENTRATION, VOLUME }, DRINK_NAME + " = " + name, null, null, null,
					TIME_CREATED + " DESC");
			CustomDrink customDrink = null;
			if (cursor.getCount() > 0 && cursor.getCount() == 1 && cursor.moveToNext()) {
				int id = cursor.getInt(cursor.getColumnIndex(ID));
				String place = cursor.getString(cursor.getColumnIndex(DRINK_NAME));
				String note = cursor.getString(cursor.getColumnIndex(NOTE));
				double rating = cursor.getDouble(cursor.getColumnIndex(PRICE));
				Timestamp timeOfLocation = Timestamp.valueOf(cursor.getString(cursor.getColumnIndex(TIME_CREATED)));
				Date time = new Date(timeOfLocation.getTime());
				double latitude = cursor.getDouble(cursor.getColumnIndex(ALCOHOL_CONCENTRATION));
				double longitude = cursor.getDouble(cursor.getColumnIndex(VOLUME));
				customDrink = new CustomDrink(id, latitude, longitude, place, note, rating, time);
			}
			db.close();
			return customDrink;
		}
		return null;
	}

	public void updateCustomDrink(Integer id, CustomDrink customDrink) {
		updateCustomDrink(id, customDrink.getName(), customDrink.getNote(), customDrink.getPrice(), customDrink.getConcentration(), customDrink.getVolume());
	}
}
